﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using JYStockAnalyser.Model;

namespace JYStockAnalyser.DA
{
    /// <summary>
    /// 估值规则数据映射器
    /// </summary>
    public class EstimateRuleMapper : ORMapper
    {
        public Dictionary<KeyValuePair<YearQuarter, YearQuarter>, int> FindAllRuleYearQuarterAndIds()
        {
            Dictionary<KeyValuePair<YearQuarter, YearQuarter>, int> result = new Dictionary<KeyValuePair<YearQuarter, YearQuarter>, int>();
            string sql = "SELECT beginYear, beginQuarter, endYear, endQuarter, id, isDefault FROM t_estimateRules";
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    bool isDefault = reader.GetBoolean(5);
                    if (isDefault)
                    {
                        YearQuarter beginQuarter = new YearQuarter(-1, -1);
                        YearQuarter endQuarter = new YearQuarter(-1, -1);
                        result.Add(new KeyValuePair<YearQuarter, YearQuarter>(beginQuarter, endQuarter), GetReaderInt32Value(reader, 4));
                    }
                    else
                    {
                        YearQuarter beginQuarter = new YearQuarter(GetReaderInt32Value(reader, 0), GetReaderInt32Value(reader, 1));
                        YearQuarter endQuarter = new YearQuarter(GetReaderInt32Value(reader, 2), GetReaderInt32Value(reader, 3));
                        result.Add(new KeyValuePair<YearQuarter, YearQuarter>(beginQuarter, endQuarter), GetReaderInt32Value(reader, 4));
                    }
                    
                }
            }
            return result;
        }

        public EstimateRule FindRuleById(int id)
        {
            string filter = String.Format(" WHERE id = {0}", id);
            return m_findRuleByFilter(filter);
        }

        public List<EstimateRule> FindAllRules()
        {
            return m_findRulesByFilter("");
        }

        public void InsertEstimateRule(EstimateRule rule)
        {
            SqlConnection cn = CreateConnection();
            cn.Open();
            SqlTransaction tran = cn.BeginTransaction(IsolationLevel.Serializable);

            try
            {
                S_CheckPeriodExists(rule, cn, tran);
                S_InsertRule(rule, cn, tran);                
                S_InsertGuideLine(rule, cn, tran);
                S_InsertCoefficients(rule, cn, tran);
                S_InsertTradeCoefficients(rule, cn, tran);
                S_InsertStockCoefficients(rule, cn, tran);
                tran.Commit();
            }
            catch (ORException orErr)
            {
                tran.Rollback();
                throw orErr;
            }
            finally
            {
                cn.Close();
            }
        }

        public void DeleteEstimateRule(int ruleID)
        {
            SqlConnection cn = CreateConnection();
            cn.Open();
            SqlTransaction tran = cn.BeginTransaction(IsolationLevel.Serializable);
            try
            {
                S_deleteGuideLines(ruleID, cn, tran);
                S_deleteCommonCoefficients(ruleID, cn, tran);
                S_deleteTradeCoefficients(ruleID, cn, tran);
                S_deleteStockCoefficients(ruleID, cn, tran);
                S_DeleteRule(ruleID, cn, tran);                
                tran.Commit();
            }
            catch (ORException orErr)
            {
                tran.Rollback();
                throw orErr;
            }
            finally
            {
                cn.Close();
            }
        }

        public void UpdateEstimateRule(EstimateRule rule)
        {
            SqlConnection cn = CreateConnection();
            cn.Open();
            SqlTransaction tran = cn.BeginTransaction(IsolationLevel.Serializable);

            try
            {
                S_CheckPeriodExistsWithOutThis(rule, cn, tran);
                S_UpdateRule(rule, cn, tran);
                S_deleteGuideLines(rule.Id, cn, tran);
                S_InsertGuideLine(rule, cn, tran);
                S_deleteCommonCoefficients(rule.Id, cn, tran);
                S_InsertCoefficients(rule, cn, tran);
                S_deleteTradeCoefficients(rule.Id, cn, tran);
                S_InsertTradeCoefficients(rule, cn, tran);
                S_deleteStockCoefficients(rule.Id, cn, tran);
                S_InsertStockCoefficients(rule, cn, tran);
                tran.Commit();
            }
            catch (ORException orErr)
            {
                tran.Rollback();
                throw orErr;
            }
            finally
            {
                cn.Close();
            }
        }

        private void S_CheckPeriodExistsWithOutThis(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string selectExistsRulePeriod = String.Format("SELECT beginYear, beginQuarter, endYear, endQuarter FROM t_estimateRules WHERE id <> {0} AND isDefault <> 1", rule.Id);
            SqlCommand cmdSelectExistsRulePeriod = new SqlCommand(selectExistsRulePeriod, cn, tran);
            using (IDataReader reader = cmdSelectExistsRulePeriod.ExecuteReader())
            {
                while (reader.Read())
                {
                    int beginYear = reader.GetInt32(0);
                    int beginQuarter = reader.GetInt32(1);
                    int endYear = reader.GetInt32(2);
                    int endQuarter = reader.GetInt32(3);

                    if (!((rule.BeginYearQuarter.CompareTo(new YearQuarter(endYear, endQuarter)) == 1 && rule.EndYearQuarter.CompareTo(new YearQuarter(endYear, endQuarter)) == 1) || (rule.BeginYearQuarter.CompareTo(new YearQuarter(beginYear, beginQuarter)) == -1 && rule.EndYearQuarter.CompareTo(new YearQuarter(beginYear, beginQuarter)) == -1)))
                    {
                        throw new ORException(ORException.ERROR_NAME_EXISTS);
                    }
                }
            }
        }

        /// <summary>
        /// 检查指定估值规则是否与现有的估值规则的有限期限相冲突
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_CheckPeriodExists(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string selectExistsRulePeriod = "SELECT beginYear, beginQuarter, endYear, endQuarter FROM t_estimateRules WHERE isDefault <> 1";
            SqlCommand cmdSelectExistsRulePeriod = new SqlCommand(selectExistsRulePeriod, cn, tran);
            using (IDataReader reader = cmdSelectExistsRulePeriod.ExecuteReader())
            {
                while (reader.Read())
                {
                    int beginYear = reader.GetInt32(0);
                    int beginQuarter = reader.GetInt32(1);
                    int endYear = reader.GetInt32(2);
                    int endQuarter = reader.GetInt32(3);

                    if (!((rule.BeginYearQuarter.CompareTo(new YearQuarter(endYear, endQuarter)) == 1 && rule.EndYearQuarter.CompareTo(new YearQuarter(endYear, endQuarter)) == 1) || (rule.BeginYearQuarter.CompareTo(new YearQuarter(beginYear, beginQuarter)) == -1 && rule.EndYearQuarter.CompareTo(new YearQuarter(beginYear, beginQuarter)) == -1)))
                    {
                    
                        throw new ORException(ORException.ERROR_NAME_EXISTS);
                    }
                }
            }
        }

        /// <summary>
        /// 删除股票系数（单步）
        /// </summary>
        /// <param name="ruleID"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_deleteStockCoefficients(int ruleID, SqlConnection cn, SqlTransaction tran)
        {
            string deleteStockCoefficientsSql = "DELETE t_estimateRuleStockCoefficients WHERE estimateRule = @estimateRule";
            SqlCommand cmdDeleteStockCoefficients = new SqlCommand(deleteStockCoefficientsSql, cn, tran);
            cmdDeleteStockCoefficients.Parameters.AddWithValue("@estimateRule", ruleID);
            try
            {
                cmdDeleteStockCoefficients.ExecuteNonQuery();
            }
            catch (SqlException sqlErr)
            {
                Loger.WriteErrorLog(sqlErr.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
        }

        /// <summary>
        /// 插入股票系数（单步）
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_InsertStockCoefficients(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string insertStockCoefficientsSql = "INSERT t_estimateRuleStockCoefficients (estimateRule, stockNO, stockName, coefficient) VALUES (@estimateRule, @stockNO, @stockName, @coefficient)";
            SqlCommand cmdInsertStockCoefficients = new SqlCommand(insertStockCoefficientsSql, cn, tran);
            foreach (StockCoefficient coe in rule.StockCoefficients)
            {
                cmdInsertStockCoefficients.Parameters.Clear();
                cmdInsertStockCoefficients.Parameters.Add(new SqlParameter("@estimateRule", rule.Id));
                cmdInsertStockCoefficients.Parameters.Add(new SqlParameter("@StockNO", coe.StockNO));
                cmdInsertStockCoefficients.Parameters.Add(new SqlParameter("@stockName", coe.StockName));
                cmdInsertStockCoefficients.Parameters.Add(new SqlParameter("@coefficient", coe.Coefficient));

                try
                {
                    cmdInsertStockCoefficients.ExecuteNonQuery();
                }
                catch (SqlException sqlErr)
                {
                    Loger.WriteErrorLog(sqlErr.ToString());
                    throw new ORException(ORException.ERROR_OTHER);
                }
            }
        }

        /// <summary>
        /// 删除行业系数（单步）
        /// </summary>
        /// <param name="ruleID"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_deleteTradeCoefficients(int ruleID, SqlConnection cn, SqlTransaction tran)
        {
            string deleteTradeCoefficientsSql = "DELETE t_estimateRuleTradeCoefficients WHERE estimateRule = @estimateRule";
            SqlCommand cmdDeleteTradeCoefficients = new SqlCommand(deleteTradeCoefficientsSql, cn, tran);
            cmdDeleteTradeCoefficients.Parameters.AddWithValue("@estimateRule", ruleID);
            try
            {
                cmdDeleteTradeCoefficients.ExecuteNonQuery();
            }
            catch (SqlException sqlErr)
            {
                Loger.WriteErrorLog(sqlErr.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
        }

        /// <summary>
        /// 插入行业系数（单步）
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_InsertTradeCoefficients(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string insertTradeCoefficientsSql = "INSERT t_estimateRuleTradeCoefficients (estimateRule, trade, value) VALUES (@estimateRule, @trade, @value)";
            SqlCommand cmdInsertTradeCoefficients = new SqlCommand(insertTradeCoefficientsSql, cn, tran);
            foreach (KeyValuePair<string, decimal> tradeCoefficient in rule.TradeCoefficients)
            {
                cmdInsertTradeCoefficients.Parameters.Clear();
                cmdInsertTradeCoefficients.Parameters.Add(new SqlParameter("@estimateRule", rule.Id));
                cmdInsertTradeCoefficients.Parameters.Add(new SqlParameter("@trade", tradeCoefficient.Key));
                cmdInsertTradeCoefficients.Parameters.Add(new SqlParameter("@value", tradeCoefficient.Value));

                try
                {
                    cmdInsertTradeCoefficients.ExecuteNonQuery();
                }
                catch (SqlException sqlErr)
                {
                    Loger.WriteErrorLog(sqlErr.ToString());
                    throw new ORException(ORException.ERROR_OTHER);
                }
            }
        }

        /// <summary>
        /// 删除通用系数（单步）
        /// </summary>
        /// <param name="ruleID"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_deleteCommonCoefficients(int ruleID, SqlConnection cn, SqlTransaction tran)
        {
            string deleteCommonCoefficientsSql = "DELETE t_estimateRuleCommonCoefficients WHERE estimateRule = @estimateRule";
            SqlCommand cmdDeleteCommonCoefficients = new SqlCommand(deleteCommonCoefficientsSql, cn, tran);
            cmdDeleteCommonCoefficients.Parameters.AddWithValue("@estimateRule", ruleID);
            try
            {
                cmdDeleteCommonCoefficients.ExecuteNonQuery();
            }
            catch (SqlException sqlErr)
            {
                Loger.WriteErrorLog(sqlErr.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
        }

        /// <summary>
        /// 插入通用系数（单步）
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_InsertCoefficients(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string insertCommonCoefficientsSql = "INSERT t_estimateRuleCommonCoefficients (estimateRule, type, value) VALUES (@estimateRule, @type, @value)";
            SqlCommand cmdInsertCommonCoefficients = new SqlCommand(insertCommonCoefficientsSql, cn, tran);
            foreach (KeyValuePair<string, decimal> commonCoefficient in rule.CommonCoefficients)
            {
                cmdInsertCommonCoefficients.Parameters.Clear();
                cmdInsertCommonCoefficients.Parameters.Add(new SqlParameter("@estimateRule", rule.Id));
                cmdInsertCommonCoefficients.Parameters.Add(new SqlParameter("@type", commonCoefficient.Key));
                cmdInsertCommonCoefficients.Parameters.Add(new SqlParameter("@value", commonCoefficient.Value));

                try
                {
                    cmdInsertCommonCoefficients.ExecuteNonQuery();
                }
                catch (SqlException sqlErr)
                {
                    Loger.WriteErrorLog(sqlErr.ToString());
                    throw new ORException(ORException.ERROR_OTHER);
                }
            }
        }

        /// <summary>
        /// 删除估值指标（单步）
        /// </summary>
        /// <param name="ruleID"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_deleteGuideLines(int ruleID, SqlConnection cn, SqlTransaction tran)
        {
            string deleteGuideLinesSql = "DELETE t_estimateRuleGuideLineTypes WHERE estimateRule = @estimateRule";
            SqlCommand cmdDeleteGuideLines = new SqlCommand(deleteGuideLinesSql, cn, tran);
            cmdDeleteGuideLines.Parameters.AddWithValue("@estimateRule", ruleID);
            try
            {
                cmdDeleteGuideLines.ExecuteNonQuery();
            }
            catch (SqlException sqlErr)
            {
                Loger.WriteErrorLog(sqlErr.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
        }

        /// <summary>
        /// 插入估值指标（单步）
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_InsertGuideLine(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string insertGuideLinesSql = "INSERT t_estimateRuleGuideLineTypes (estimateRule, guideLineType) VALUES (@estimateRule, @guideLineType)";
            SqlCommand cmdInsertGuideLines = new SqlCommand(insertGuideLinesSql, cn, tran);
            foreach (GuideLineType line in rule.GuideLineTypes)
            {
                cmdInsertGuideLines.Parameters.Clear();
                cmdInsertGuideLines.Parameters.Add(new SqlParameter("@estimateRule", rule.Id));
                cmdInsertGuideLines.Parameters.Add(new SqlParameter("@guideLineType", line.NO));
                try
                {
                    cmdInsertGuideLines.ExecuteNonQuery();
                }
                catch (SqlException sqlErr)
                {
                    Loger.WriteErrorLog(sqlErr.ToString());
                    throw new ORException(ORException.ERROR_OTHER);
                }
            }
        }

        /// <summary>
        /// 更新估值规则（单步）
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private void S_UpdateRule(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string sqlUpdateRule = "UPDATE t_estimateRules SET beginYear = @beginYear, beginQuarter = @beginQuarter, endYear = @endYear, endQuarter = @endQuarter, isDefault = @isDefault, AsafetyCoefficient = @AsafetyCoefficient, BsafetyCoefficient = @BsafetyCoefficient, CsafetyCoefficient = @CsafetyCoefficient, DsafetyCoefficient = @DsafetyCoefficient, EsafetyCoefficient = @EsafetyCoefficient WHERE id = @id";

            SqlCommand cmdUpdateRule = new SqlCommand(sqlUpdateRule, cn, tran);
            cmdUpdateRule.Parameters.Add(new SqlParameter("@id", rule.Id));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@beginYear", rule.BeginYearQuarter.Year));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@beginQuarter", rule.BeginYearQuarter.Quarter));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@endYear", rule.EndYearQuarter.Year));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@endQuarter", rule.EndYearQuarter.Quarter));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@isDefault", rule.IsDefault));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@AsafetyCoefficient", rule.ASafetyCoefficient));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@BsafetyCoefficient", rule.BSafetyCoefficient));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@CsafetyCoefficient", rule.CSafetyCoefficient));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@DsafetyCoefficient", rule.DSafetyCoefficient));
            cmdUpdateRule.Parameters.Add(new SqlParameter("@EsafetyCoefficient", rule.ESafetyCoefficient));

            try
            {
                cmdUpdateRule.ExecuteNonQuery();
            }
            catch (SqlException sqlErr)
            {
                Loger.WriteErrorLog(sqlErr.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
        }

        private void S_DeleteRule(int ruleID, SqlConnection cn, SqlTransaction tran)
        {
            string deleteSql = "DELETE t_estimateRules WHERE id = @ruleID";

            SqlCommand deleteRuleCmd = new SqlCommand(deleteSql, cn, tran);

            deleteRuleCmd.Parameters.Add(new SqlParameter("@ruleID", ruleID));

            try
            {
                deleteRuleCmd.ExecuteNonQuery();
            }
            catch (SqlException sqlErr)
            {
                Loger.WriteErrorLog(sqlErr.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
        }

        /// <summary>
        /// 插入估值规则（单步）
        /// </summary>
        /// <param name="rule"></param>
        /// <param name="cn"></param>
        /// <param name="tran"></param>
        private int S_InsertRule(EstimateRule rule, SqlConnection cn, SqlTransaction tran)
        {
            string insertRuleSql = "INSERT t_estimateRules (beginYear, beginQuarter, endYear, endQuarter, isDefault, AsafetyCoefficient, BsafetyCoefficient, CsafetyCoefficient, DsafetyCoefficient, EsafetyCoefficient) VALUES (@beginYear, @beginQuarter, @endYear, @endQuarter, @isDefault, @AsafetyCoefficient, @BsafetyCoefficient, @CsafetyCoefficient, @DsafetyCoefficient, @EsafetyCoefficient)  SELECT @@IDENTITY ";

            SqlCommand insertRuleCmd = new SqlCommand(insertRuleSql, cn, tran);
          
            insertRuleCmd.Parameters.Add(new SqlParameter("@beginYear", rule.BeginYearQuarter.Year));
            insertRuleCmd.Parameters.Add(new SqlParameter("@beginQuarter", rule.BeginYearQuarter.Quarter));
            insertRuleCmd.Parameters.Add(new SqlParameter("@endYear", rule.EndYearQuarter.Year));
            insertRuleCmd.Parameters.Add(new SqlParameter("@endQuarter", rule.EndYearQuarter.Quarter));
            insertRuleCmd.Parameters.Add(new SqlParameter("@isDefault", rule.IsDefault));
            insertRuleCmd.Parameters.Add(new SqlParameter("@AsafetyCoefficient", rule.ASafetyCoefficient));
            insertRuleCmd.Parameters.Add(new SqlParameter("@BsafetyCoefficient", rule.BSafetyCoefficient));
            insertRuleCmd.Parameters.Add(new SqlParameter("@CsafetyCoefficient", rule.CSafetyCoefficient));
            insertRuleCmd.Parameters.Add(new SqlParameter("@DsafetyCoefficient", rule.DSafetyCoefficient));
            insertRuleCmd.Parameters.Add(new SqlParameter("@EsafetyCoefficient", rule.ESafetyCoefficient));

            try
            {
                rule.Id = Convert.ToInt32(insertRuleCmd.ExecuteScalar());
                return rule.Id;
            }
            catch (SqlException sqlErr)
            {
                Loger.WriteErrorLog(sqlErr.ToString());
                throw new ORException(ORException.ERROR_OTHER);
            }
        }

        #region private methods

        private EstimateRule m_findRuleByFilter(string filter)
        {
            List<EstimateRule> rules = m_findRulesByFilter(filter);
            return rules.Count == 0 ? null : rules[0];
        }

        private List<EstimateRule> m_findRulesByFilter(string filter)
        {
            string sql = "SELECT id, beginYear, beginQuarter, endYear, endQuarter, isDefault, AsafetyCoefficient, BsafetyCoefficient, CsafetyCoefficient, DsafetyCoefficient, EsafetyCoefficient FROM t_estimateRules" + filter + " ORDER BY beginYear, beginQuarter";
            List<EstimateRule> rules = new List<EstimateRule>();
            using (SqlDataReader reader = Excute_Reader(sql))
            {
                while (reader.Read())
                {
                    EstimateRule rule = new EstimateRule();
                    rule.Id = GetReaderInt32Value(reader, 0);
                    rule.BeginYearQuarter.Year = GetReaderInt32Value(reader, 1);
                    rule.BeginYearQuarter.Quarter = GetReaderInt32Value(reader, 2);
                    rule.EndYearQuarter.Year = GetReaderInt32Value(reader, 3);
                    rule.EndYearQuarter.Quarter = GetReaderInt32Value(reader, 4);
                    rule.IsDefault = GetReaderBooleanValue(reader, 5);
                    rule.ASafetyCoefficient = GetReaderDecimalValue(reader, 6);
                    rule.BSafetyCoefficient = GetReaderDecimalValue(reader, 7);
                    rule.CSafetyCoefficient = GetReaderDecimalValue(reader, 8);
                    rule.DSafetyCoefficient = GetReaderDecimalValue(reader, 9);
                    rule.ESafetyCoefficient = GetReaderDecimalValue(reader, 10);

                    string selectGuideLinesSql = String.Format("SELECT gls.name, gls.[no], gls.isImportant, gls.unit FROM t_GLs gls JOIN t_estimateRuleGuideLineTypes ruleTypes ON gls.no = ruleTypes.guideLineType WHERE ruleTypes.estimateRule = {0}", rule.Id);
                    using (SqlDataReader selectGuideLinesReader = Excute_Reader(selectGuideLinesSql))
                    {
                        while (selectGuideLinesReader.Read())
                        {
                            GuideLineType type = new GuideLineType();
                            type.Name = GetReaderString(selectGuideLinesReader, 0);
                            type.NO = GetReaderInt32Value(selectGuideLinesReader, 1);
                            type.IsImportant = GetReaderBooleanValue(selectGuideLinesReader, 2);
                            type.Unit = GetReaderString(selectGuideLinesReader, 3);
                            rule.GuideLineTypes.Add(type);
                        }
                    }

                    string selectCommonCoefficientsSql = String.Format("SELECT type, [value] FROM t_estimateRuleCommonCoefficients WHERE estimateRule = {0}", rule.Id);
                    using (SqlDataReader selectCommonCoefficientsReader = Excute_Reader(selectCommonCoefficientsSql))
                    {
                        while (selectCommonCoefficientsReader.Read())
                        {
                            rule.CommonCoefficients.Add(GetReaderString(selectCommonCoefficientsReader, 0), GetReaderDecimalValue(selectCommonCoefficientsReader, 1));
                        }
                    }

                    string selectTradeCoefficientsSql = String.Format("SELECT trade, [value] FROM t_estimateRuleTradeCoefficients WHERE estimateRule = {0}", rule.Id);
                    using (SqlDataReader selectTradeCoefficientsReader = Excute_Reader(selectTradeCoefficientsSql))
                    {
                        while (selectTradeCoefficientsReader.Read())
                        {
                            rule.TradeCoefficients.Add(GetReaderString(selectTradeCoefficientsReader, 0), GetReaderDecimalValue(selectTradeCoefficientsReader, 1));
                        }
                    }

                    string selectStockCoefficientsSql = String.Format("SELECT stockNO, stockName, coefficient FROM t_estimateRuleStockCoefficients WHERE estimateRule = {0}", rule.Id);
                    using (SqlDataReader selectStockCoefficientsReader = Excute_Reader(selectStockCoefficientsSql))
                    {
                        while (selectStockCoefficientsReader.Read())
                        {
                            StockCoefficient coe = new StockCoefficient();
                            coe.StockNO = selectStockCoefficientsReader.GetValue(0).ToString();
                            coe.StockName = GetReaderString(selectStockCoefficientsReader, 1);
                            coe.Coefficient = GetReaderDecimalValue(selectStockCoefficientsReader, 2);

                            rule.StockCoefficients.Add(coe);
                        }
                    } 

                    rules.Add(rule);
                }
            }
            return rules;
        }

        #endregion
    }
}
